﻿using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using CE.Database;
using CE.Security;

/// <summary>
/// Summary description for HRAccountDB
/// </summary>
namespace CE.HR
{
    public class HRAccountDB
    {
        public HRAccountDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }
        public Int64 InsertUpdate(HRAccount account)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocHRInsertUpdateSingleItem";
            SortedList _sPara = new SortedList();
            #region Parameter
            _sPara.Add("@HRID", account.HRID);
            _sPara.Add("@Country", account.Country);
            _sPara.Add("@Name", account.Name);
            _sPara.Add("@Email", account.Email.ToLower());
            _sPara.Add("@AuthCode", account.AuthCode);
            _sPara.Add("@AuthSalt", account.AuthSalt);
            _sPara.Add("@AuthHint", account.AuthHint);
            _sPara.Add("@HRTypeId", account.HRTypeId);
            _sPara.Add("@RefHRId", account.RefHRID);
            _sPara.Add("@Status", account.Status);
            _sPara.Add("@Info1", account.Info1);
            _sPara.Add("@Info2", account.Info2);
            _sPara.Add("@Info3", account.Info3);
            _sPara.Add("@Info4", account.Info4);
            _sPara.Add("@Info5", account.Info5);
            _sPara.Add("@Info6", account.Info6);
            _sPara.Add("@Info7", account.Info7);
            _sPara.Add("@Info8", account.Info8);
            _sPara.Add("@Info9", account.Info9);
            _sPara.Add("@Info10", account.Info10);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);
            return _result;
        }

        public HRAccount CheckEmail(string email)
        {
            HRAccount _account = null;
            MyDatabase _db = new MyDatabase();
            string strSQL = "Select HRID, Country, Name, Email, AuthHint, AuthCode, AuthSalt, HRTypeId, Status, RefHRId from tblHR Where [Email] = @Email AND [Status] = 1";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", email);

            using (DataTable _dt = _db.GetDataTable(strSQL, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new HRAccount();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public HRAccount CheckEmail(string Email, int UserTypeId)
        {
            HRAccount _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemByEmail]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@Status", true);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new HRAccount();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public HRAccount GetUserAccount(string Email, int HRTypeId)
        {
            HRAccount _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocHRSelectSingleItemIsEmailExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@HRTypeId", HRTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new HRAccount();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public HRAccount GetUserAccountByAuthKey(string AuthSalt, string GUID)
        {
            HRAccount _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemByAuthKey]";

            SortedList _spara = new SortedList();
            _spara.Add("@AuthKey", AuthSalt);
            _spara.Add("@GUID", GUID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new HRAccount();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public bool IsAlreadyExist(string Email, int UserTypeId)
        {
            bool _result = false;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemIsEmailExist]";

            SortedList _spara = new SortedList();
            _spara.Add("@Email", Email);
            _spara.Add("@UsrTypeId", UserTypeId);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _result = true;
                }
            }
            return _result;
        }

        public HRAccount GetAccount(Int64 HRID)
        {
            HRAccount _account = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocHRSelectSingleItem]";

            SortedList _spara = new SortedList();
            _spara.Add("@HRID", HRID);

            using (DataTable _dt = _db.GetDataTableBySP(_spName, _spara))
            {
                if (_dt != null && _dt.Rows.Count > 0)
                {
                    _account = new HRAccount();
                    _account = FillDataRecord(_dt);
                }
            }
            return _account;
        }

        public string GetGUID(Int64 UID)
        {
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserSelectSingleItemGUID]";

            SortedList _spara = new SortedList();
            _spara.Add("@UID", UID);

            object _result = _db.ExecuteScalarSP(_spName, _spara);

            return _result.ToString();
        }

        public bool ActivateAccount(string GUID)
        {
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserActivateAccount]";

            SortedList _spara = new SortedList();
            _spara.Add("@GUID", GUID);

            int _result = _db.ExecuteNonSqlSP(_spName, _spara);

            return _result > 0;
        }

        public bool ResetPassword(string NewPassword, string AuthKey, string GUID)
        {
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocUserResetPassword]";

            SortedList _spara = new SortedList();
            _spara.Add("@NewPassword", NewPassword);
            _spara.Add("@GUID", GUID);
            _spara.Add("@AuthKey", AuthKey);

            int _result = _db.ExecuteNonSqlSP(_spName, _spara);

            return _result > 0;
        }

        public Int64 DeleteUserByID(int HRID)
        {
            Int64 _result = -1;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocHRDeleteSingleItemByID]";

            SortedList _spara = new SortedList();
            _spara.Add("@HRID", HRID);

            _result = _db.ExecuteSP(_spName, _spara);
            return _result;
        }

        private HRAccount FillDataRecord(DataTable myDataTable)
        {
            HRAccount myAccount = new HRAccount();
            myAccount.HRID = Convert.ToInt64(myDataTable.Rows[0]["HRID"].ToString());
            myAccount.Country = myDataTable.Rows[0]["Country"].ToString();
            myAccount.Name = myDataTable.Rows[0]["Name"].ToString();
            myAccount.Email = myDataTable.Rows[0]["Email"].ToString();
            myAccount.AuthCode = myDataTable.Rows[0]["AuthCode"].ToString();
            myAccount.AuthSalt = myDataTable.Rows[0]["AuthSalt"].ToString();
            myAccount.HRTypeId = Convert.ToInt32(myDataTable.Rows[0]["HRTypeId"].ToString());
            myAccount.RefHRID = Convert.ToInt64(myDataTable.Rows[0]["RefHRId"].ToString());
            myAccount.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            if (myDataTable.Rows[0]["Info1"] != null)
            {
                myAccount.Info1 = myDataTable.Rows[0]["Info1"].ToString();
            }
            if (myDataTable.Rows[0]["Info2"] != null)
            {
                myAccount.Info2 = myDataTable.Rows[0]["Info2"].ToString();
            }

            return myAccount;
        }

        private HRAccount FillDataRecord(IDataRecord myDataRecord)
        {
            HRAccount myAccount = new HRAccount();
            myAccount.HRID = myDataRecord.GetInt64(myDataRecord.GetOrdinal("HRID"));
            myAccount.Country = myDataRecord.GetString(myDataRecord.GetOrdinal("Country"));
            myAccount.Name = myDataRecord.GetString(myDataRecord.GetOrdinal("Name"));
            myAccount.Email = myDataRecord.GetString(myDataRecord.GetOrdinal("Email"));
            myAccount.AuthCode = myDataRecord.GetString(myDataRecord.GetOrdinal("AuthCode"));
            myAccount.AuthSalt = myDataRecord.GetString(myDataRecord.GetOrdinal("AuthSalt"));
            myAccount.RefHRID = myDataRecord.GetInt64(myDataRecord.GetOrdinal("RefHRId"));
            myAccount.Status = myDataRecord.GetBoolean(myDataRecord.GetOrdinal("Status"));
            return myAccount;
        }
    }
}